﻿--获取部门员工档案
CREATE PROCEDURE [dbo].[proc_Employee_GetDepartmentList]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@docount bit,
	@username nvarchar (50),
	@DepartmentId varchar(2000),
	@searchTxt nvarchar(50)
)
as

--公司Id
declare @companyid nvarchar(4)
set @companyid = (select companyid from Employee where username=@username)

declare @sql_Count nvarchar(2000)
declare @sql_Temp nvarchar(2000)
declare @sql nvarchar (2000)

set nocount on
	if(@docount=1)
		begin
		set @sql_Count = 
				+ ' select *,1 as CompanyName,2 as DepartmentName'
				+ ' from employee'
				+ ' where  CompanyId =' + @companyid +' and  departmentid in ('+@DepartmentId+')' +' and EName like ''%'+@searchTxt+'%'''
		exec (@sql_Count)
		end
	else
	begin
	
	
	set @sql = 
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' set rowcount ' + @endIndex
		+ ' insert into @indextable (nid)'
		+ ' select id'
		+ ' from employee'
		+ ' where  CompanyId =' + @companyid +' and  departmentid in ( '+@DepartmentId+')'  +' and EName like ''%'+@searchTxt+'%'''

		+' select' 
		+' E.id'
		+' ,E.UserName'
		+' ,E.EName'
		+' ,E.Enumber'
		+' ,E.ETempNumber'
		+' ,E.Sex'
		+' ,E.CompanyId'
		+' ,(select companyname from company where id=E.CompanyId) as CompanyName'
		+' ,E.DepartmentId'
		+' ,(select dname from Department where id=E.DepartmentId) as DepartmentName'
		+' ,E.State'
		+' ,E.PostId'
		+' ,E.GradeId'
		+' ,E.WageNumber'
		+' ,E.RuzhiDate'
		+' ,E.LizhiDate'
		+' ,E.Birthday'
		+' ,E.Rtx'
		+' ,E.OfficePhone'
		+' ,E.HomePhone'
		+' ,E.MobilePhone'
		+' ,E.Email'
		+' ,E.IDcard'
		+' ,E.Address'
		+' ,E.Education'
		+' ,E.HeadPic'
		+' ,E.UpName'
		+' ,E.UpTime'
	+' from employee E'
	+' inner join @indextable t on '
	+' E.id=t.nid'
	+' where' 
		+' t.id between '+ @startIndex +' and '+ @endIndex 
	+' order by t.id'

exec (@sql)
	end
set nocount off
RETURN

